package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

import control.ConnectionManage;
import db.parking.beans.Orders;
import db.parking.beans.StatsticPerDay;

/**
 * AdminStatistics manages all requests From,To table Statistics
 * @author Operations
 *
 */
public class AdminStatistics {
	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	/**
	 * insert statistics per one day	
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static boolean insertStasticsPerDay() throws Exception {	
		// complaintID is autoIncremented on Insert
		String sql = "INSERT into statsticsperday " +
				"(lotNo , date , realizedordersnum , canceledordersnum, subscribersnum, subscriberscarsnum, latenessnum) "
				+ "VALUES (?, ?, ?, ?, ?,?,?)";
		
		ResultSet rs = null;
		long time = System.currentTimeMillis(); // create current time
		Timestamp endDay = new Timestamp(time); // set time like 2014-12-01
													// 00:00:00
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
		
		
		Timestamp startDay = new Timestamp(endDay.getTime());
		Calendar cal = Calendar.getInstance();
		cal.setTime(startDay);
		cal.add(Calendar.DAY_OF_WEEK, -1); // add day for the first week
		startDay.setTime(cal.getTime().getTime()); // add day for the first week
		
		
		Timestamp startMinus28 =new Timestamp(endDay.getTime());
		Calendar cal2 = Calendar.getInstance();
		cal2.setTime(startDay);
		cal.add(Calendar.DAY_OF_WEEK, -28); // add day for the first week
		startMinus28.setTime(cal.getTime().getTime()); // add day for the first week
		
	

		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
			for (int i = 1; i <= 6 ; i++) { // run for all lots
				stmt.setInt(1, i);		//lotNo
				stmt.setTimestamp(2, startDay);	//day date
				int realizedorders=AdminOrders.numberOfRealizedOrders(startDay,endDay, i);  //get num of realizedorders
				stmt.setInt(3, realizedorders);	//set num realizedorders	
				
				int canceledorders=AdminCanceledOrders.canceldOrdersCount(startDay,endDay, i);  //get num of canceledorders
				stmt.setInt(4, canceledorders);	//set num of canceledorders
				
				int subscribers= AdminSubscriber.getSubscribersNumInLot(startDay,startMinus28, i); //get num of subscribers
				stmt.setInt(5, subscribers);	//set num of subscribers
				
				int subscriberscars= AdminSubscriber.getSubscribersNumInLotMoreThanOneCar(startDay,startMinus28, i); //get num of subscribers that have more than one car
				stmt.setInt(6, subscriberscars);	//set num of subscribers
				
				int lateness = AdminOrders.numberOfParkingLateness(startDay, endDay, i); //get num of lateness
				stmt.setInt(7, lateness );	//set num of lateness
				
				//rs= 
						stmt.executeUpdate();
				
			}
		} catch (SQLException e) {
			System.err.println(e + "AdminStatistics-> insertStasticsPerDay");
			return false;
		} finally {
			if (rs != null)
				rs.close();
		}
		return true;

	}

	/**
	 * @param firstDay lastDay
	 *            return all the 
	 * @return statisticsPerDay array
	 */
	@SuppressWarnings("deprecation")
	public static StatsticPerDay [] getStatsticDuringTime(Timestamp firstDay, Timestamp lastDay) throws SQLException{
		String sql = "SELECT * " +
				"FROM stasticsperday "
				+ "WHERE date>=? and date <=?"
				+ "VALUES (?, ?)";
		ResultSet rs = null;
		firstDay.setHours(0);
		firstDay.setMinutes(0);
		firstDay.setSeconds(0);
		firstDay.setNanos(0);// 00:00:00
		
		lastDay.setHours(0);
		lastDay.setMinutes(0);
		lastDay.setSeconds(0);
		lastDay.setNanos(0);// 00:00:00

		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
				stmt.setTimestamp(1, firstDay); // day date
				stmt.setTimestamp(2, lastDay); // day date
				rs = stmt.executeQuery();
				rs.last();
				int totalRows = rs.getRow();
				rs.beforeFirst();
				StatsticPerDay bean[] = new StatsticPerDay [totalRows];
				for (int i = 0; i < totalRows; i++)
					bean[i] = new StatsticPerDay();
				int rowCount = 0;
				while (rs.next()) {
					bean[rowCount].setLatenessNo(rs.getInt("lotNo"));
					bean[rowCount].setDate(rs.getTimestamp("date"));
					bean[rowCount].setRealizedordersNo(rs.getInt("realizedordersNo"));
					bean[rowCount].setCanceledordersNo(rs.getInt("canceledordersNo"));
					bean[rowCount].setSubscribersNo(rs.getInt("subscribersNo"));
					bean[rowCount].setSubscriberscarsNo(rs.getInt("subscriberscarsNo"));
					bean[rowCount].setLatenessNo(rs.getInt("latenessNo"));
					rowCount++;
				}
				return bean;
			} catch (SQLException e) {
				System.err.println(e);
				return null;
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		}
	
	
	/**
	 * @param firstDay lastDay
	 *            return all the 
	 * @return statisticsPerDay array
	 */

	public static StatsticPerDay [] getAllStatsticPerDay() throws SQLException{
		String sql = "SELECT * FROM stasticsperday ";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {
				rs = stmt.executeQuery();
				rs.last();
				int totalRows = rs.getRow();
				rs.beforeFirst();
				StatsticPerDay bean[] = new StatsticPerDay [totalRows];
				for (int i = 0; i < totalRows; i++)
					bean[i] = new StatsticPerDay();
				int rowCount = 0;
				while (rs.next()) {
					bean[rowCount].setLatenessNo(rs.getInt("lotNo"));
					bean[rowCount].setDate(rs.getTimestamp("date"));
					bean[rowCount].setRealizedordersNo(rs.getInt("realizedordersNo"));
					bean[rowCount].setCanceledordersNo(rs.getInt("canceledordersNo"));
					bean[rowCount].setSubscribersNo(rs.getInt("subscribersNo"));
					bean[rowCount].setSubscriberscarsNo(rs.getInt("subscriberscarsNo"));
					bean[rowCount].setLatenessNo(rs.getInt("latenessNo"));
					rowCount++;
				}
				return bean;
			} catch (SQLException e) {
				System.err.println(e);
				return null;
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		}
}